﻿using DMSkin.Socket;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NDK.Module.Model;
using BZ.Logger;

namespace NDK.LocalUtils.Yield
{
    /// <summary>
    /// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化
    /// ExecuteNonQuery()执行增删改操作的利器
    /// ExecuteScalar()返回查询的第一行第一列的数据
    /// ExecuteReader()执行查询操作利器,ExecuteReader()方法不是事务方法的理想选择
    /// </summary>
    public static class SQLiteHandle
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string connectionString = "Data Source=D:\\NDK-Software\\Config\\Statistics.db;Password=123";

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                if (!Directory.Exists("D:\\NDK-Software\\Config"))
                {
                    Directory.CreateDirectory("D:\\NDK-Software\\Config");
                }
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        /// <summary>
        /// 获取数据库里数据表名称的集合
        /// </summary>
        /// <returns>表名称的集合</returns>
        public static List<string> GetSQLTable()
        {
            //IL_0038: Expected O, but got Unknown
            try
            {
                string sqlstr = "SELECT NAME FROM sqlite_master WHERE TYPE='table'";
                DbDataReader dr = ExecuteReader(sqlstr);
                List<string> tables = new List<string>();
                while (dr.Read())
                {
                    tables.Add(dr.GetString(0));
                }
                return tables;
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"GetSQLTable --> " + ex.ToString(), LogType.Error);
                return null;
            }
        }

        /// <summary>
        /// 获取数据表里列名称的集合
        /// </summary>
        /// <param name="tablename">数据表的名称</param>
        /// <returns>列名称的集合</returns>
        public static List<string> GetTableColumns(string tablename)
        {
            //IL_0048: Expected O, but got Unknown
            try
            {
                string sqlstr = $"PRAGMA TABLE_INFO('{tablename}')";
                DbDataReader dr = ExecuteReader(sqlstr);
                List<string> ls = new List<string>();
                while (dr.Read())
                {
                    ls.Add($"{dr[1]}");
                }
                return ls;
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"GetTableColumns --> " + ex.ToString(), LogType.Error);
                return null;
            }
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>true,执行成功|false,执行失败</returns>
        public static bool ExecuteNonQuery(SQLiteCommand cmd)
        {
            //IL_0001: Unknown result type (might be due to invalid IL or missing references)
            //IL_0007: Expected O, but got Unknown
            //IL_0068: Expected O, but got Unknown
            //IL_002c: Unknown result type (might be due to invalid IL or missing references)
            //IL_0032: Expected O, but got Unknown
            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, ((DbCommand)(object)cmd).CommandType, ((DbCommand)(object)cmd).CommandText);
                try
                {
                    ((DbCommand)(object)cmd).ExecuteNonQuery();
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (SQLiteException val)
                {
                    SQLiteException ex = val;
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteNonQuery --> " + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <returns>true,执行成功|false,执行失败</returns>
        public static bool ExecuteNonQuery(string commandText)
        {
            //IL_007f: Expected O, but got Unknown
            //IL_0040: Unknown result type (might be due to invalid IL or missing references)
            //IL_0046: Expected O, but got Unknown
            //IL_004b: Unknown result type (might be due to invalid IL or missing references)
            //IL_0052: Expected O, but got Unknown
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            if (commandText == null || commandText.Length == 0)
            {
                return false;
            }
            int ret = 0;
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, CommandType.Text, commandText);
                try
                {
                    ret = ((DbCommand)(object)cmd).ExecuteNonQuery();
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (SQLiteException val)
                {
                    SQLiteException ex = val;
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteNonQuery -->  " + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 批量执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名(数组形式)</param>
        /// <returns>true,执行成功|false,执行失败</returns>
        public static bool ExecuteNonQuery(string[] commandText)
        {
            //IL_00d5: Expected O, but got Unknown
            //IL_003b: Unknown result type (might be due to invalid IL or missing references)
            //IL_0041: Expected O, but got Unknown
            //IL_0046: Unknown result type (might be due to invalid IL or missing references)
            //IL_004d: Expected O, but got Unknown
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            if (commandText == null || commandText.Length == 0)
            {
                return false;
            }
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                if (((DbConnection)(object)con).State != ConnectionState.Open)
                {
                    ((DbConnection)(object)con).Open();
                }
                cmd.Connection = con;
                trans = (cmd.Transaction = con.BeginTransaction(IsolationLevel.ReadCommitted));
                ((DbCommand)(object)cmd).CommandType = CommandType.Text;
                try
                {
                    for (int i = 0; i < commandText.Length; i++)
                    {
                        ((DbCommand)(object)cmd).CommandText = commandText[i];
                        ((DbCommand)(object)cmd).ExecuteNonQuery();
                    }
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (SQLiteException val2)
                {
                    SQLiteException ex = val2;
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteNonQuery -->  " + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="cmdParms">SQL参数对象</param>
        /// <returns>true,执行成功|false,执行失败</returns>
        public static bool ExecuteNonQuery(string commandText, params SQLiteParameter[] cmdParms)
        {
            //IL_003c: Unknown result type (might be due to invalid IL or missing references)
            //IL_0042: Expected O, but got Unknown
            //IL_0047: Unknown result type (might be due to invalid IL or missing references)
            //IL_004e: Expected O, but got Unknown
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            if (commandText == null || commandText.Length == 0)
            {
                return false;
            }
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, useTrans: true, CommandType.Text, commandText, cmdParms);
                try
                {
                    ((DbCommand)(object)cmd).ExecuteNonQuery();
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteNonQuery -->" + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 执行数据库操作同时返回执行后查询所得的第1行第1列数据(只有查询语句才有返回值)
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="obj">第1行第1列数据(只有查询语句才有返回值)</param>
        /// <returns>true,执行成功|false,执行失败</returns>
        public static bool ExecuteScalar(SQLiteCommand cmd, out object obj)
        {
            //IL_0064: Expected O, but got Unknown
            //IL_0029: Unknown result type (might be due to invalid IL or missing references)
            //IL_002f: Expected O, but got Unknown
            obj = null;
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, ((DbCommand)(object)cmd).CommandType, ((DbCommand)(object)cmd).CommandText);
                try
                {
                    obj = ((DbCommand)(object)cmd).ExecuteScalar();
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (SQLiteException val)
                {
                    SQLiteException ex = val;
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteScalar --> " + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 执行数据库操作同时返回执行后查询所得的第1行第1列数据(只有查询语句才有返回值)
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="obj">第1行第1列数据(只有查询语句才有返回值)</param>
        /// <returns></returns>
        public static bool ExecuteScalar(string commandText, out object obj)
        {
            //IL_007f: Expected O, but got Unknown
            //IL_003f: Unknown result type (might be due to invalid IL or missing references)
            //IL_0045: Expected O, but got Unknown
            //IL_004a: Unknown result type (might be due to invalid IL or missing references)
            //IL_0051: Expected O, but got Unknown
            obj = null;
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                return false;
            }
            if (commandText == null || commandText.Length == 0)
            {
                return false;
            }
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            try
            {
                SQLiteTransaction trans = null;
                PrepareCommand(cmd, con, ref trans, true, CommandType.Text, commandText);
                try
                {
                    obj = ((DbCommand)(object)cmd).ExecuteScalar();
                    ((DbTransaction)(object)trans).Commit();
                    return true;
                }
                catch (SQLiteException val)
                {
                    SQLiteException ex = val;
                    ((DbTransaction)(object)trans).Rollback();
                    Common.AppendUiLog.Log($"ExecuteScalar -->  " + ex.ToString(), LogType.Error);
                    return false;
                }
            }
            finally
            {
                ((IDisposable)con)?.Dispose();
            }
        }

        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(SQLiteCommand cmd)
        {
            //IL_0062: Expected O, but got Unknown
            //IL_002f: Unknown result type (might be due to invalid IL or missing references)
            //IL_0035: Expected O, but got Unknown
            DbDataReader reader = null;
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, ((DbCommand)(object)cmd).CommandType, ((DbCommand)(object)cmd).CommandText);
            try
            {
                return (DbDataReader)(object)cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"ExecuteReader -->   " + ex.ToString(), LogType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 执行数据库查询，返回SqlDataReader对象
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <returns>SqlDataReader对象</returns>
        public static DbDataReader ExecuteReader(string commandText)
        {
            //IL_0081: Expected O, but got Unknown
            //IL_0052: Unknown result type (might be due to invalid IL or missing references)
            //IL_0058: Expected O, but got Unknown
            //IL_0058: Unknown result type (might be due to invalid IL or missing references)
            //IL_005e: Expected O, but got Unknown
            DbDataReader reader = null;
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, CommandType.Text, commandText);
            try
            {
                return (DbDataReader)(object)cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"ExecuteReader -->    " + ex.ToString(), LogType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 执行数据库查询，返回DataSet对象
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(SQLiteCommand cmd)
        {
            //IL_000c: Unknown result type (might be due to invalid IL or missing references)
            //IL_0012: Expected O, but got Unknown
            //IL_0045: Expected O, but got Unknown
            //IL_0032: Unknown result type (might be due to invalid IL or missing references)
            //IL_0038: Expected O, but got Unknown
            DataSet ds = new DataSet();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, ((DbCommand)(object)cmd).CommandType, ((DbCommand)(object)cmd).CommandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                ((DataAdapter)(object)sda).Fill(ds);
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"ExecuteDataSet --> " + ex.ToString(), LogType.Error);
                throw ex;
            }
            finally
            {
                if (cmd.Connection != null && ((DbConnection)(object)cmd.Connection).State == ConnectionState.Open)
                {
                    ((DbConnection)(object)cmd.Connection).Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行数据库查询，返回DataSet对象
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string commandText)
        {
            //IL_008d: Expected O, but got Unknown
            //IL_0056: Unknown result type (might be due to invalid IL or missing references)
            //IL_005c: Expected O, but got Unknown
            //IL_005c: Unknown result type (might be due to invalid IL or missing references)
            //IL_0062: Expected O, but got Unknown
            //IL_0078: Unknown result type (might be due to invalid IL or missing references)
            //IL_007f: Expected O, but got Unknown
            DataSet ds = new DataSet();
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, CommandType.Text, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                ((DataAdapter)(object)sda).Fill(ds);
            }
            catch (SQLiteException val)
            {
                SQLiteException ex = val;
                Common.AppendUiLog.Log($"ExecuteDataSet --> " + ex.ToString(), LogType.Error);
                throw ex;
            }
            finally
            {
                if (con != null && ((DbConnection)(object)con).State == ConnectionState.Open)
                {
                    ((DbConnection)(object)con).Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行数据库查询，返回DataTable对象
        /// </summary>
        /// <param name="commandText">执行语句或存储过程名</param>
        /// <param name="commandType">执行类型(默认语句)</param>
        /// <returns>DataTable对象</returns>
        public static DataTable ExecuteDataTable(string commandText, CommandType commandType = CommandType.Text)
        {
            //IL_0054: Unknown result type (might be due to invalid IL or missing references)
            //IL_005a: Expected O, but got Unknown
            //IL_005a: Unknown result type (might be due to invalid IL or missing references)
            //IL_0060: Expected O, but got Unknown
            //IL_0076: Unknown result type (might be due to invalid IL or missing references)
            //IL_007d: Expected O, but got Unknown
            if (ConnectionString == null || ConnectionString.Length == 0)
            {
                throw new ArgumentNullException("connectionString");
            }
            if (commandText == null || commandText.Length == 0)
            {
                throw new ArgumentNullException("commandText");
            }
            DataTable dt = new DataTable();
            SQLiteConnection con = new SQLiteConnection(ConnectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                ((DbDataAdapter)(object)sda).Fill(dt);
            }
            catch (Exception ex)
            {
                Common.AppendUiLog.Log($"ExecuteDataTable --> " + ex.ToString(), LogType.Error);
                throw ex;
            }
            finally
            {
                if (con != null && ((DbConnection)(object)con).State == ConnectionState.Open)
                {
                    ((DbConnection)(object)con).Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="useTrans">是否使用事务</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">SQL Text</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
        {
            if (((DbConnection)(object)conn).State != ConnectionState.Open)
            {
                conn.SetPassword("123");
                ((DbConnection)(object)conn).Open();
            }
            cmd.Connection = conn;
            ((DbCommand)(object)cmd).CommandText = cmdText;
            if (useTrans)
            {
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = trans;
            }
            ((DbCommand)(object)cmd).CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}
